<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>The .NET DataSet and OleDbDataAdapter Objects</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">The .NET DataSet and OleDbDataAdapter Objects</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>

<asp:datagrid id="dgrResult" runat="server" />

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect As String
   strConnect = ctlConnectStrings.JetConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '1861003%'"
   outSelect.innerText = strSelect   'and display it

   'declare a variable to hold a DataSet object
   'note that we have to create it outside the Try..Catch block
   'as this is a separate block and so is a different scope
   Dim objDataSet As New DataSet()

   Try

      'create a new Connection object using the connection string
      Dim objConnect As New OleDbConnection(strConnect)

      'create a new DataAdapter using the connection object and select statement
      Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

      'fill the dataset with data from the DataAdapter object
      objDataAdapter.Fill(objDataSet, "Books")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'create a DataView object for the Books table in the DataSet
   Dim objDataView As New DataView(objDataSet.Tables("Books"))

   'assign the DataView object to the DataGrid control
   dgrResult.DataSource = objDataView
   dgrResult.DataBind()   'and bind (display) the data

End Sub
</script>

<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
